package com.offlinerssreader.model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.util.LinkedList;

public class User extends GenericModel {

	/*
	 * CREATE  TABLE `d99a86dfe0417471aab92d9cd7aee5695`.`users` (
	 * `id` INT NOT NULL AUTO_INCREMENT ,
	 * `firstName` VARCHAR(45) NOT NULL ,
	 * `lastName` VARCHAR(45) NOT NULL ,
	 * `mail` VARCHAR(45) NOT NULL ,
	 * `password` VARCHAR(45) NOT NULL ,
	 * PRIMARY KEY (`id`) ); 
	 */
	
	private int id;
	public String firstName;
	public String lastName;
	public String email;
	public String password;
	
	public static final String TABLE_NAME = "users";
	public static final String COL_NAME_ID = "id";
	public static final String COL_NAME_FIRST_NAME = "firstName";
	public static final String COL_NAME_LAST_NAME = "lastName";
	public static final String COL_NAME_EMAIL = "mail";
	public static final String COL_NAME_PASSWORD = "password";

	public User() {
		super();
	}
	
	public User(int id) throws Exception {
		super();
		
		String query;
		Statement statement;
		ResultSet result;
		
		query = "SELECT " + User.COL_NAME_EMAIL + " FROM " +  User.TABLE_NAME + " WHERE " + User.COL_NAME_ID + " = '" + id + "'";

		if (this.conn.isClosed())
			this.conn = DBConnections.getConnection();

		statement = this.conn.createStatement();
		result = statement.executeQuery(query);
		result.next();
		
		load(result.getString(User.COL_NAME_EMAIL));
	}

	@Override
	public boolean save() throws Exception {
		String query;
		Statement statement;
		if (this.id == 0)
			throw new Exception("Trying to delete without id");
		
		query = "UPDATE " +  User.TABLE_NAME + " SET VALUES " +
				User.COL_NAME_FIRST_NAME + "='" + this.firstName + "', " +
				User.COL_NAME_LAST_NAME + "='" + this.lastName + "', '" +
				User.COL_NAME_EMAIL + "='" + this.email + "', " + 
				User.COL_NAME_PASSWORD + "='" + this.password + "', " +
				" WHERE " + User.COL_NAME_ID + " = '" + this.id + "'";
		
		if (this.conn.isClosed()) {
			this.conn = DBConnections.getConnection();
			if (this.conn == null) {
				return false;
			}
		}

		statement = this.conn.createStatement();
		if (statement.executeUpdate(query) < 1) {
			return false;
		}
		return true;
	}

	@Override
	public boolean delete() throws Exception {
		String query;
		Statement statement;
		if (this.id == 0)
			throw new Exception("Trying to delete without id");
		
		query = "DELETE FROM " +  User.TABLE_NAME + " WHERE " + User.COL_NAME_ID + " = '" + this.id + "'";
		
		if (this.conn.isClosed()) {
			this.conn = DBConnections.getConnection();
			if (this.conn == null) {
				return false;
			}
		}

		statement = this.conn.createStatement();
		if (statement.executeUpdate(query) < 1) {
			return false;
		}
		return true;
	}

	@Override
	public boolean load(Object key) throws Exception {
		this.email = key.toString();
		String query;
		Statement statement;
		ResultSet result;
		
		query = "SELECT * FROM " +  User.TABLE_NAME + " WHERE " + User.COL_NAME_EMAIL + " = '" + email + "'";
		
		if (this.conn.isClosed()) {
			this.conn = DBConnections.getConnection();
			if (this.conn == null) {
				return false;
			}
		}

		statement = this.conn.createStatement();
		result = statement.executeQuery(query);
		if (!result.next()) {
			result.close();
			return false;
		}

		this.id = result.getInt(User.COL_NAME_ID);
		this.firstName = result.getString(User.COL_NAME_FIRST_NAME);
		this.lastName = result.getString(User.COL_NAME_LAST_NAME);
		this.email = result.getString(User.COL_NAME_EMAIL);
		this.password = result.getString(User.COL_NAME_PASSWORD);
		result.close();
		return true;
	}

	@Override
	public boolean create() throws Exception {
		String query;
		Statement statement;

		query = "INSERT INTO " +  User.TABLE_NAME + "("
				+ User.COL_NAME_FIRST_NAME + ", "
				+ User.COL_NAME_LAST_NAME + ", "
				+ User.COL_NAME_EMAIL + ", "
				+ User.COL_NAME_PASSWORD + ""
				+ ") VALUES (" +
				"'" + this.firstName + "', " +
				"'" + this.lastName + "', " +
				"'" + this.email + "', " +
				"'" + this.password + "')";
		
		System.out.println(query);
		
		if (this.conn.isClosed()) {
			this.conn = DBConnections.getConnection();
			if (this.conn == null) {
				return false;
			}
		}
		
		statement = this.conn.createStatement();
		if (statement.executeUpdate(query) < 1) {
			return false;
		}
		return true;
	}

	public boolean checkValues() {
		return (this.email != null && this.firstName != null && this.firstName != null && this.password != null);
	}
	
	public String getPassword() {
		return this.password;
	}
	
	public static String getCreateTable() {
		return "CREATE TABLE IF NOT EXISTS `" + TABLE_NAME + "` ("
				+ "`" + COL_NAME_ID + "` INT NOT NULL AUTO_INCREMENT ,"
				+ "`" + COL_NAME_FIRST_NAME + "` VARCHAR(45) NOT NULL ,"
				+ "`" + COL_NAME_LAST_NAME + "` VARCHAR(45) NOT NULL ,"
				+ "`" + COL_NAME_EMAIL + "` VARCHAR(45) NOT NULL ,"
				+ "`" + COL_NAME_PASSWORD + "` VARCHAR(45) NOT NULL ,"
				+ "PRIMARY KEY (`" + COL_NAME_ID + "`) );";
	}
	
	public int getId() {
		return this.id;
	}
	
	public boolean isRegistered() {
		String query;
		Statement statement;
		ResultSet result;
		
		query = "SELECT " + COL_NAME_ID + " FROM " + TABLE_NAME + " WHERE `" + COL_NAME_EMAIL + "`='" + this.email + "'";
		try {
			if (this.conn.isClosed()) {
				this.conn = DBConnections.getConnection();
				if (this.conn == null) {
					return false;
				}
			}

			statement = this.conn.createStatement();
			result = statement.executeQuery(query);
			if (!result.next()) {
				result.close();
				return false;
			}
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}

	public LinkedList<RssFeed> getUserFeeds() throws Exception {
		String query;
		Statement statement;
		ResultSet result;
		LinkedList<RssFeed> list = new LinkedList<RssFeed>();
		int feedId;
		
		query = "SELECT " + RssFeed.COL_NAME_ID + " FROM " + RssFeed.TABLE_NAME + " WHERE `" + RssFeed.COL_NAME_USER + "`='" + this.id + "'";
		try {
			if (this.conn.isClosed()) {
				this.conn = DBConnections.getConnection();
				if (this.conn == null) {
					return null;
				}
			}

			statement = this.conn.createStatement();
			result = statement.executeQuery(query);
			while (result.next()) {
				feedId = result.getInt(RssFeed.COL_NAME_ID);
				RssFeed feed = new RssFeed(feedId);
				list.add(feed);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
}
